<?php

class M_rekap extends CI_Model
{
    public function __construct() {
        parent::__construct();
    }
    public function perhs_mode($mode)
    {
    	$what = '';
    	switch ($mode) {
    		case 'volume':
    			$what = " SUM( d.Jumlah) ";
    			break;
    		case 'nilai':
    			$what = " SUM( d.nilai_usd) ";
    			break;
    		default:
    			$what = " COUNT( d.id) ";
    			break;
    	}
    	return $what;
    }
    public function perhs_count($mode,$d1,$d2,$rekomendasi=FALSE,$byImportir=FALSE)
    {
    	
    	$what = $this->perhs_mode($mode);
    	
    	$rekomendasi_sql_1 = $rekomendasi ? ' LEFT JOIN hs hs ON hs.HS_No = d.HS  ' : '';
    	$rekomendasi_sql_2 = $rekomendasi ? " hs.rekomendasi = '1' AND (" : '';
    	$rekomendasi_sql_3 = $rekomendasi ? ')' : '';

    	$sql = "SELECT $what as total
				FROM header h 
				LEFT JOIN negara n ON h.Kode_Negara_Muat = n.kode 
				LEFT JOIN detail d ON d.header_id = h.id $rekomendasi_sql_1
				WHERE $rekomendasi_sql_2 h.Tanggal_COI >= '$d1' AND h.Tanggal_COI <= '$d2' $rekomendasi_sql_3";

		$rs = $this->db->query($sql)->row()->total;
		$rs = $rs ? $rs : 0;

		return $mode == 'nilai' ? number_format($rs,2) : $rs;
    }
    public function perhs($mode,$d1,$d2,$batas=0,$rekomendasi=FALSE,$byImportir=FALSE)
    {
    	// echo($batas);
    	// die($batas);
    	$rekomendasi_sql_1 = $rekomendasi ? ' LEFT JOIN hs hs ON hs.HS_No = d.HS  ' : '';
    	$rekomendasi_sql_2 = $rekomendasi ? " hs.rekomendasi = '1' AND (" : '';
    	$rekomendasi_sql_3 = $rekomendasi ? ')' : '';

    	$what = $this->perhs_mode($mode);
    	$sql_default = "SELECT d.HS, $what jml,
				DATE_FORMAT(h.Tanggal_COI,'%Y-%m') as bln,
				n.uraian as negara_muat
				FROM header h 
				LEFT JOIN negara n ON h.Kode_Negara_Muat = n.kode 
				LEFT JOIN detail d ON d.header_id = h.id $rekomendasi_sql_1
				WHERE $rekomendasi_sql_2  h.Tanggal_COI >= '$d1' AND h.Tanggal_COI <= '$d2' $rekomendasi_sql_3
				GROUP BY d.HS,h.Kode_Negara_Muat
				ORDER BY  jml DESC,h.Kode_Negara_Muat ASC,h.Tanggal_COI ASC";
		$sql_importir = "SELECT d.HS, $what jml,
				DATE_FORMAT(h.Tanggal_COI,'%Y-%m') as bln,
				h.Importir as negara_muat
				FROM header h 
				
				LEFT JOIN detail d ON d.header_id = h.id $rekomendasi_sql_1
				WHERE $rekomendasi_sql_2  h.Tanggal_COI >= '$d1' AND h.Tanggal_COI <= '$d2' $rekomendasi_sql_3
				GROUP BY d.HS,h.Importir
				ORDER BY  jml DESC,h.Importir ASC,h.Tanggal_COI ASC"; 
		
		$sql = $byImportir? $sql_importir : $sql_default;
				
		$rs = $this->db->query($sql)->result();

		$data = array(
			'record' => array(),
			'series' => month_range($d1,$d2,1)
		);
		if($batas == 0)
			$batas = 1000;
		

		foreach ($rs as $r) {
			if( !isset($data['record'][$r->negara_muat]) )
			{
				$data['record'][$r->negara_muat] = array(
					'total' => 0
				);
			}

			if( !isset($data['record'][$r->negara_muat][$r->HS]))
			{
				$data['record'][$r->negara_muat][$r->HS] = array();
			}
			if( !isset($data['record'][$r->negara_muat][$r->HS][$r->bln]))
			{
				$data['record'][$r->negara_muat][$r->HS][$r->bln] = 0;
			}
			$data['record'][$r->negara_muat][$r->HS][$r->bln] += $r->jml;
			$data['record'][$r->negara_muat]['total'] += $r->jml;

			// if(!in_array($r->bln, $data['series']))
			// {
			// 	$data['series'][] = $r->bln;
			// }

		}
		$prefix = $byImportir ? 'Perusahaan' : 'Negara';
		if( $batas != 1000)
		{

			$iter = 1;
			$new_data = array(
				'record' => array()
			);
			foreach ($data['record'] as $negara_muat => $series_data) {
				if( $iter > $batas )
				{
					if( !isset($new_data['record']["$prefix Lainya"]))
					{
						$new_data['record']["$prefix Lainya"] = array(
							'total' => 0
						);
					}
					foreach ($series_data as $k => $v) {
						if( $k == 'total')
						{
							$new_data['record']["$prefix Lainya"]['total'] += $v; 
						}
						else
						{
							if( !isset($new_data['record']["$prefix Lainya"][$k]))
							{
								$new_data['record']["$prefix Lainya"][$k] = array();
							}
							foreach ($v as $bth => $count) {
								if( !isset($new_data['record']["$prefix Lainya"][$k][$bth]))
								{
									$new_data['record']["$prefix Lainya"][$k][$bth] = 0;
								}
								$new_data['record']["$prefix Lainya"][$k][$bth] += $count;
							}
						}						
					}
					
				}
				else
				{
					$new_data['record'][$negara_muat] = $series_data;
				}
				$iter += 1;
			}
			$new_data['series'] = $data['series'];
			unset($data);
			return $new_data;
		}

		return $data;
    }

}